Mega Code Archive
SQL PLUS Session Environment
1) $$plsql_line and $$plsql_unit
2) $IF $$plsql_optimize_level
3) $IF $$plsql_optimize_level IS NULL OR $$plsql_optimize_level IS NOT NULL
4) A dropped table is preserved in the recycle bin until purged
5) ACCEPT
6) Accept input and pass into a query
7) Accept x prompt Enter date[yyyy-mm-dd]
8) Add dollar signs to the output with $990 99
9) Adjust your session time zone to -08
10) ALTER SESSION SET optimizer_dynamic_sampling
11) ALTER SESSION SET plsql_ccflags
12) ALTER SESSION SET PLSQL_WARNINGS = ENABLE
13) ALTER SESSION SET plsql_warnings = disable
14) ALTER SESSION SET SQL_TRACE = TRUE
15) ALTER SESSION SET SQL_TRACE = TRUEFALSE
16) Alter session set current_schema=scott
17) Alter session set hash_area_size
18) Alter session set nls_date_format = SYYYYMMDD HH24MISS
19) Alter session set NLS_DATE_FORMAT=DD-MM-YYYY
20) Alter session set nls_date_format=dd-mm-yyyy, nls_language=Dutch, nls_currency=Eur
21) Alter session set OPTIMIZER_MODE = choose
22) Alter session set optimizer_index_cost_adj, alter session set optimizer_index_caching
23) Alter session set optimizer_max_permutations=80000
24) Alter session set optimizer_mode=all_rows
25) Alter session set optimizer_mode=first_rows
26) Alter session set query_rewrite_integrity=enforced
27) Alter session set sort_area_size = 102400
28) Alter session set sort_area_size = 102400000
29) Alter session set workarea_size_policy=manual
30) Alter session to change the time zone
31) Alter system flush shared_pool
32) Alter system quiesce restricted
33) Alter system resume
34) Alter system suspend
35) An ORDER BY clause that mirrors the BREAK command
36) Analyze and autotrace full outer join and union
37) Analyze and autotrace single column key and multi-column key
38) Analyze and autotrace table with primary key
39) Analyze compute statistics on table with index
40) Analyze index t_idx validate structure
41) Analyze table after creating index
42) Analyze table compute statistics
43) Analyze table compute statistics for table for all indexes for all indexed columns
44) Analyze table estimate statistics
45) Analyze table students compute statistics
46) Analyze table t compute statistics for table for columns id
47) Analyze table t compute statistics for table, for all indexes, for all indexed columns
48) Analyze table TABLENAME compute statistics
49) Analyze table with user defined column type
50) Authorize user in stored procedure
51) Autotrace an self join
52) Autotrace count()
53) AUTOTRACE exists (subquery)
54) Autotrace explain a select statement
55) Autotrace merge command
56) AUTOTRACE table joining
57) AUTOTRACE table joining and aggregate function
58) Autotrace table withwithout an index
59) Autotrace traceonly statistics
60) Bind variables can even be referenced by SQL queries
61) BREAK may contain more than one ON clause
62) Break on department skip 1 on job
63) Break on two
64) Break the report on some column to produce easy-to-read output
65) Call USER to get the name of current user
66) Change the line size
67) Change the page size
68) Check $$PLSQL_OPTIMIZE_LEVEL
69) Check pre-defined variables
70) CLEAR BREAKS
71) CLEAR COMPUTES
72) Clear screens
73) COLUMN first_name FORMAT A10 WORD_WRAP HEADING Name JUSTIFY CENTER
74) COLUMN first_name FORMAT A13 WORD_WRAPPED
75) COLUMN first_name HEADING FIRST_NAME FORMAT A13 WORD_WRAPPED
76) COLUMN format in action
77) Column LIKE another Column
78) COLUMN salary FORMAT $99 99 HEADING Salary JUSTIFY RIGHT
79) COLUMN Salary FORMAT 909 99
80) Combine v$statname and v$mystat
81) COMPUTE calculates an aggregate value and place the result at the break point
82) Compute may be used in conjunction with BREAK to give summary results
83) Connect to sql plus as sysdba
84) Controlling the Report
85) Copy column format with col like
86) Count user name
87) Create a procedure to test variables being treated as bind variables
88) Define _editor
89) Define name
90) Define variable
91) Define variable and print its value out
92) Define variable and set value
93) Define variable and use it in sql statement
94) Define_editor=Notepad for Windows
95) Define_editor=vi for UNIX
96) Determine the database time zone
97) Determine your session time zone
98) Execution plan for the specified statement_id
99) Explain sql statement
100) Extracts data from the emp table and writes it to a text file in a comma-delimited format
101) Feedback shows how many records were selected if it exceeds a certain amount
102) For alphanumeric columns, if the column is too short, it will be displayed on multiple lines
103) Format interval type column
104) FORMAT number column as 999,999
105) Format report with CSS
106) Format the number column with COLUMN salary FORMAT 99999999
107) Formatting Columns
108) Get and set client information by using
109) Get current program with sys_context(userenv,sessionid) and v$session
110) Get current user, session user and current schema by using sys_context
111) If a blank line is desired
112) If the column format is too small for a number column, # will be used
113) If there are decimals or if commas are desired, use the 99999 99
114) If your session time zone is not USCentral Standard Time (-06
115) Introduction to SQL Tuning - Workshop
116) LINESIZE
117) LONG controls how much of a LONG or CLOB sqlplus displays by default
118) Markup html entmap off
119) Multiple COMPUTEs are also allowable
120) Nls_currency
121) Nls_language
122) Numbers can also be output with leading zeros or dollar signs
123) Output result to html table
124) PAGESIZE
125) Pagesize defaults to 24
126) PAUSE
127) Print variable out
128) Prompt This is a demonstration
129) Purge recyclebin
130) Query v$archived_log
131) Ratio_to_report
132) Reference environment parameter in procedure
133) Remarks in Scripts with Ttitle and Btitle
134) Reporting Tools in Oracles SQLPlus
135) Run the same two queries using binded variables
136) SCOTT is a demonstration account
137) SELECTs using the GROUP BY clause
138) Serveroutput controls whether your DBMS_OUTPUT PUT_LINE calls go into the bit bucket
139) Set arraysize 5
140) SET AUTOTRACE OFF
141) SET AUTOTRACE TRACE EXP and EXPLAIN PLAN output
142) Set autotrace traceonly explain
143) Set autotrace traceonly explain for bitmap index
144) Set autotrace traceonly explain, and condition
145) Set autotrace traceonly statistics
146) Set autotrace traceonly statistics for select from tableName
147) Set autotrace traceonly statistics to trace a function
148) Set colsep
149) Set feedback 100
150) Set feedback 4
151) Set feedback onoff
152) Set linesize 20
153) Set long
154) Set long 50000
155) Set long 999
156) Set markup html on
157) Set noprint for a column
158) Set null [NA]
159) Set numformat
160) Set numformat 09999 99
161) Set numformat 99999
162) Set numwidth 10
163) Set pagesize 10
164) Set pagesize 100 linesize 120 newpage 0 feedback off
165) Set pause message
166) Set pause off
167) Set pause off pagesize 42
168) Set pause on
169) SET SCAN OFFON
170) SET SERVEROUTPUT ON for PLSQL
171) Set serveroutput on format truncated
172) Set serveroutput on format word_wrapped
173) Set serveroutput on size 1000000
174) Set signature prompt in sqlplus to username@database
175) SET string to display when value is NULL
176) Set timing on
177) SET verify
178) Setting up the server to communicate
179) Shorten the varchar column format with COLUMN command
180) SHOW ALL displays all parameters
181) Show all markup
182) Show and set autocommit
183) Show autocommit
184) Show concat
185) Show define
186) Show feedback
187) Show markup
188) Show pages
189) Show parameter optimizer_features
190) Show verify
191) Skip page
192) Spool result to a text file
193) Spool to a file with compute
194) SQLPLUS commands
195) SQLPlus is a tool for interfacing with the Oracle database
196) SYS
197) SYS_CONTEXT (WEB_CTX,WEBUSER)
198) Sys_context(userenv,sessionid)
199) SYSTEM is an administrator account
200) The echo feature displays the command on the screen when executed
201) The syntax for connecting to a remote database service is
202) The syntax to connect via SQLPlus from the command line looks like the following
203) TIMING table copying
204) Time the PLSQL block
205) To run statements in Oracle from the command line, a semicolon must be added
206) To undo all column formatting
207) TTitle ON and OFF
208) Two Break commands
209) Two digits
210) Undefine variable
211) Use defined-variable in update set statement
212) Use Spoll to save query to a file
213) Use sys_context in where statement
214) Use SYS_CONTEXT to retrieve the database name
215) Use TTITLE and BTITLE
216) Use userenv
217) Use variable command to define variable to reference cursor
218) Userenv(sessionid)
219) Variable binding